package com.huasoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.huasoft.domain.File;
import com.huasoft.util.JDBConnection;

public class FileDao {

	public boolean save(File file) {
		String sql = "insert into files values(null,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, file.getType());
			ps.setString(2, file.getTitle());
			ps.setString(3, file.getNumber());
			ps.setString(4, file.getTime());
			ps.setString(5, file.getLevel());
			ps.setString(6, file.getStatus());
			int rowNumber = ps.executeUpdate();
			if (rowNumber > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}

	public List<File> listFile(String types, String title, String levels,
			PageCondition pageCondition) {
		StringBuffer sql = new StringBuffer("select * from files where 1=1");
		Connection con = null;
		Statement st = null;
		List<File> files = new ArrayList<File>();
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if (types != null && !types.equals("0")) {
				sql.append(" and types = '" + types + "'");
			}
			if (title != null && !"".equals(title)) {
				sql.append(" and title like '%" + title + "%'");
			}
			if (levels != null && !levels.equals("0")) {
				sql.append(" and levels = '" + levels + "'");
			}
			System.out.println(sql.toString());
			int begin = (pageCondition.getPage() - 1)
					* pageCondition.getPageSize();
			System.out.println("begin" + begin);
			int end = pageCondition.getPageSize();
			System.out.println("end" + end);
			sql.append(" limit " + begin + "," + end);
			System.out.println(sql);
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			while (rs.next()) {
				File file = new File();
				file.setId(rs.getInt("id"));
				file.setType(rs.getString("types"));
				file.setTitle(rs.getString("title"));
				file.setNumber(rs.getString("numbers"));
				file.setLevel(rs.getString("levels"));
				file.setStatus(rs.getString("status"));
				file.setTime(rs.getString("times"));
				files.add(file);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		System.out.println(files.size() + "abx");
		return files;
	}

	public void totalPage(String types, String title, String levels,
			PageCondition pageCondition) {
		StringBuffer sql = new StringBuffer(
				"select count(*) from files where 1=1");
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			if (types != null && !types.equals("0")) {
				sql.append(" and types = '" + types + "'");
			}
			if (title != null && !"".equals(title)) {
				sql.append(" and title like '%" + title + "%'");
			}
			if (levels != null && !levels.equals("0")) {
				sql.append(" and levels = '" + levels + "'");
			}
			st = con.createStatement();
			rs = st.executeQuery(sql.toString());
			rs.next();
			int num = rs.getInt(1);
			if (num % pageCondition.getPageSize() == 0) {
				pageCondition.setTotalPage(num / pageCondition.getPageSize());
			} else {
				pageCondition.setTotalPage(num / pageCondition.getPageSize()
						+ 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public File findById(Integer id) {
		String sql = "select * from files where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		File file = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				file = new File();
				file.setId(rs.getInt("id"));
				file.setType(rs.getString("types"));
				file.setTitle(rs.getString("title"));
				file.setNumber(rs.getString("numbers"));
				file.setLevel(rs.getString("levels"));
				file.setStatus(rs.getString("status"));
				file.setTime(rs.getString("times"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return file;
	}

	public boolean update(File file) {
		String sql = "update files set types=? , title=? "
				+ ", numbers=? , times=? , levels=? where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, file.getType());
			ps.setString(2, file.getTitle());
			ps.setString(3, file.getNumber());
			ps.setString(4, file.getTime());
			ps.setString(5, file.getLevel());
			ps.setInt(6, file.getId());
			int rowNumber = ps.executeUpdate();
			System.out.println(rowNumber);
			if (rowNumber > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
	}

	public boolean deleteById(Integer id) {
		String sql = "delete from files where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			int rowNum = ps.executeUpdate();
			if (rowNum > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public boolean updateStatus(File file) {
		String sql = "update files set status=? where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JDBConnection.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, file.getStatus());
			ps.setInt(2, file.getId());
			int rowNumber = ps.executeUpdate();
			System.out.println(rowNumber);
			if (rowNumber > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	public List<File> findAll() {
		String sql = "select * from files";
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<File> files = null;
		try {
			con = JDBConnection.getConnection();
			st = con.createStatement();
			rs = st.executeQuery(sql);
			files=new ArrayList<File>();
			while (rs.next()) {
				File file = new File();
				file.setId(rs.getInt("id"));
				file.setType(rs.getString("types"));
				file.setTitle(rs.getString("title"));
				file.setNumber(rs.getString("numbers"));
				file.setLevel(rs.getString("levels"));
				file.setStatus(rs.getString("status"));
				file.setTime(rs.getString("times"));
				System.out.println(file);
				System.out.println(file.getType());
				files.add(file);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				JDBConnection.closeConnection(con, st, rs);
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		System.out.println(files);
		return files;
	}
}
